﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model;
namespace DB
{
    public class DbMovie : IDbMovie
    {
        private DbConnection con;
        private const string DB_NAME = "Movie_xp"; //tjek
        
        public DbMovie()
        {
            con = DbConnection.GetInstance();
        }
        public int getNextID() // til at hjælpe, skal ikke bruges under movie
        {

            String query = null;
            int nextInt = 1;
            try
            {
                query = "select MAX(serialNo) from " + DB_NAME;
                SqlCommand command = con.GetDbCommand(query);
                nextInt = (Int32)command.ExecuteScalar();
                nextInt++;
                con.CloseConnection();
            }
            catch (Exception e)
            {
            }
            return nextInt;

        }

        public int InsertMovie(Movie movie)
        {
            String query = null;
            int rc = -1;
            try
            {
                query = @"INSERT INTO Movie_xp VALUES (@serialNo, @title, @length,@genre, @releasedate,@ranking, @revId)";
                //Console.WriteLine(query);
                SqlCommand command = con.GetDbCommand(query);
                
                command.Parameters.AddWithValue("@serialNo", movie.SerialNo);
                Console.WriteLine("1");
                command.Parameters.AddWithValue("@title", movie.Title);
                Console.WriteLine("1");
                command.Parameters.AddWithValue("@length", movie.Length);
                Console.WriteLine("1"); 
                command.Parameters.AddWithValue("@genre", movie.Genre);
                Console.WriteLine("1"); 
                command.Parameters.AddWithValue("@releasedate", movie.ReleaseDate);
                Console.WriteLine("1"); 
                command.Parameters.AddWithValue("@ranking", movie.Ranking);
                Console.WriteLine("1"); 
                command.Parameters.AddWithValue("@revId", DBNull.Value);
                Console.WriteLine("1");
                rc = command.ExecuteNonQuery();
            }
            catch (SqlException sqle)
            {
                Console.WriteLine("SQLFejl i dblayer(insertmovie): " + sqle.Message);
                throw sqle;
            }
            catch (Exception e)
            {
                Console.WriteLine("AlmFejl i dblayer(insertmovie): " + e.Message);
                throw e;
            }
            con.CloseConnection();
            return rc;


        }
        public int DeleteMovie(Movie movie)
        {
            String query = null;
            int rc = -1;
            try
            {
                query = @"DELETE FROM Movie_xp WHERE serialNo = @serialNo";
                //Console.WriteLine(query);
                SqlCommand command = con.GetDbCommand(query);
                command.Parameters.AddWithValue("@serialNo", movie.SerialNo);
                rc = command.ExecuteNonQuery();
            }
            catch (SqlException sqle)
            {
                throw sqle;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.CloseConnection();
            }
            return rc;
        }
        public void updateMovie(Movie movie)
        {
        }

        public Movie FindMovie(string serialNo)
        {
            String query = null;
            Movie newMovie = null;

            try
            {
                query = "select * from " + DB_NAME + " Where serialNo = " + serialNo;
                SqlCommand command = con.GetDbCommand(query);
                SqlDataReader data = command.ExecuteReader();
                while (data.Read())
                {

                    newMovie = (BuildMovie(data));

                }
                data.Close();
                con.CloseConnection();

            }

            catch (Exception e)
            {
            }
            return newMovie;
        }

        public List<Movie> FindAllMovies()
        {
            String query = null;
            List<Movie> movies = new List<Movie>();
            SqlDataReader reader = null;
            try
            {

                Movie newMovie = new Movie();
                query = @"SELECT * FROM Movie_xp ORDER BY title ASC";
                //Console.WriteLine(query);
                SqlCommand command = con.GetDbCommand(query);
                reader = command.ExecuteReader();
                while(reader.Read())
                {

                    movies.Add(BuildMovie(reader));

                }
                reader.Close();
                
                
            }
            catch (SqlException sqle)
            {
                Console.WriteLine(sqle.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                con.CloseConnection();
            }
            return movies;
        }

        private Movie BuildMovie(SqlDataReader dbReader)
        {
            Movie movieObj = new Movie();

            try
            {
                movieObj.SerialNo = (String)dbReader["serialNo"];
                movieObj.Title = (String)dbReader["title"];
                movieObj.Length = (String)dbReader["length"];
                movieObj.Genre = (String)dbReader["genre"];
                DateTime theDate = (DateTime)dbReader["releaseDate"];
                movieObj.ReleaseDate = theDate;
                Double newdouble = Convert.ToDouble((Decimal)dbReader["ranking"]);
                movieObj.Ranking = newdouble;
                movieObj.Review = null;//dbReview.buildReview(dbReader); // skal bygge et review fra dbreview
}
            catch(Exception e)
            {
                Console.WriteLine("Error in buiding movieobject");
                Console.WriteLine(e.Message);
            }

            return movieObj;
        }
    }
}
